const {
  MYSQL_USER,
  MYSQL_PWD,
  MYSQL_DB,
  MYSQL_HOST,
  MYSQL_PORT,
  COLUMN_TYPE,
} = require("../server-env");

const knex = require("knex")({
  client: "mysql2",
  connection: {
    host: MYSQL_HOST,
    port: MYSQL_PORT,
    user: MYSQL_USER,
    password: MYSQL_PWD,
    // database: "mysql",
    database: MYSQL_DB,
  },
  pool: { min: 2, max: 10 },
  log: {
    warn(message) {
      console.warn(message);
    },
    error(message) {
      console.error(message);
    },
    deprecate(message) {
      console.log(message);
    },
    debug(message) {
      console.log(message);
    },
  },
});

knex.listDatabases = async () => {
  if (knex.client.config.client === "mysql2") {
    const databases = await knex.raw("SHOW DATABASES");
    return databases?.[0]?.map((item) => item.Database) || [];
  }

  return [];
};

knex.listTables = async () => {
  if (knex.client.config.client === "mysql2") {
    const tables = await knex.schema.raw("SHOW TABLES");
    return tables?.[0]?.map((item) => item["Tables_in_mysql"]) || [];
  }

  return [];
};

knex.listTableColumns = async (tableName) => {
  if (!/^[0-9a-z_]+$/i.test(tableName)) {
    throw "tableName 不符合规范[0-9a-z_]+: " + tableName;
  }

  if (knex.client.config.client === "mysql2") {
    return await knex.schema.raw(`DESCRIBE ${tableName}`).then((columns) => {
      return (
        columns?.[0]?.map((item) => {
          return {
            field: item["Field"],
            type: item["Type"],
            defaultValue: item["Default"],
            isPrimaryKey: item["Key"] === "PRI",
            extra: item["Extra"],
          };
        }) || []
      );
    });
  }

  return [];
};

knex.createDatabase = async (databaseName) => {
  if (!/^[0-9a-z_]+$/i.test(databaseName)) {
    throw "databaseName 不符合规范[0-9a-z_]+: " + databaseName;
  }

  if (knex.client.config.client === "mysql2") {
    return await knex.raw(`CREATE DATABASE IF NOT EXISTS ${databaseName}`);
  }
};

knex.createTable = async (tableName, columns) => {
  if (!/^[0-9a-z_]+$/i.test(tableName)) {
    throw "tableName 不符合规范[0-9a-z_]+: " + tableName;
  }

  if (!(columns?.length > 0)) {
    throw "columns 不能为空";
  }

  if (knex.client.config.client === "mysql2") {
    const hasTable = await knex.schema.hasTable(tableName)

    if (hasTable) {
      await knex.schema.dropTable(tableName)
    }

    return await knex.schema.createTable(tableName, function (table) {
      columns.forEach((item) => {
        const type = item.type;
        let column

        if (item.increments) {
          column = table.bigIncrements(item.field, { primaryKey: item.isPrimaryKey });
        } else if (type === COLUMN_TYPE.DATETIME) {
          column = table.dateTime(item.field);
        } else if (type === COLUMN_TYPE.TIMESTAMP) {
          column = table.timestamp(item.field).defaultTo(knex.fn.now());
        } else if (type === COLUMN_TYPE.STRING) {
          column = table.string(item.field, item.maxLen || 20);
        } else if (type === COLUMN_TYPE.INT) {
          column = table.integer(item.field);
        } else if (type === COLUMN_TYPE.DECIMAL) {
          column = table.decimal(item.field, item.maxLen || 10, item.precision || 2);
        }

        if (item.indexed) {
          column?.index()
        }

        if (item.unique) {
          column?.unique()
        }

      });
    });
  }
};

module.exports = knex;
